MySQL Subquery
A Subquery is query within an another query.
This is mainly used to take decisions based on the results of the subquery.
Consider the 2 tables.
Customers Table
| cust_id | name | description | age |
|---|---|---|---|
| 101 | Peter | Engineer | 32 |
| 102 | Joseph | Developer | 30 |
| 103 | John | Leader | 28 |
| 104 | Stephen | Scientist | 45 |
| 105 | Suzi | Carpenter | 26 |
| 106 | Bob | Actor | 25 |
Orders Table
| order_id | cust_id | prod_name | order_date | amount |
|---|---|---|---|---|
| 1 | 101 | Laptop | 2022-01-10 | 45000 |
| 2 | 103 | Desktop | 2022-02-12 | 35000 |
| 3 | 106 | Iphone | 2022-02-15 | 65000 |
| 4 | 104 | Mobile | 2022-03-05 | 12000 |
Syntax for Subquery
select column_1, column_2, ...
from table_name
where column_name = (subquery);
Example
SELECT *
FROM customers
WHERE age = (
SELECT MAX(age)
FROM customers
);
Output
| cust_id | name | description | age |
|---|---|---|---|
| 104 | Stephen | Scientist | 45 |
The above query will list the customers whose age is the maximum age which is 31.
Example 2
SELECT *
FROM customers
WHERE cust_id IN (
SELECT cust_id
FROM orders
);
Output
| cust_id | name | description | age |
|---|---|---|---|
| 101 | Peter | Engineer | 32 |
| 103 | John | Leader | 28 |
| 104 | Stephen | Scientist | 45 |
| 106 | Bob | Actor | 25 |
The above query will list the cutomers who has any order in the the orders table.
SQL Subquery and JOIN
The subquery can be used to acheive the same result that is returned by the JOIN clause.
Example query with inner join
select customer.name from
customer
inner join orders
on customer.cust_id=orders.cust_id;
Example query with subquery
SELECT name
FROM customer
WHERE cust_id IN (
SELECT cust_id
FROM Orders
);
Both the above query returns the same result as below.
| name |
|---|
| Peter |
| John |
| Stephen |
| Bob |
Though both the query returns the same result, its better to use the JOIN instead of subquery. This is because JOIN executes faster than the subquery.